package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;



import es.ats.codington.festival.domain.Event;
import es.ats.codington.festival.domain.Places;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.domain.Zoo;
import es.ats.codington.festival.interfaces.daos.IVisitorDAOImp;
import es.ats.codington.festival.utils.FERSDataConnection;


public class VisitorDAOImp implements IVisitorDAOImp{
	
	public VisitorDAOImp(){};

	//	This source code tries to establish a connection to a MySQL server
	//	and displays a message concerning the error if connection fails
	
	//Following method creates a connection and returns it
	
	
	 public int loginDAO(Visitor visitor, Connection con ) {
				
		int aRows = 0;		
		ResultSet resultset=null;
		
		try{	
			//Connect with DATABASE
			PreparedStatement statement = con.prepareStatement("Select * from people where userName=? and password= ? ;");
			statement.setString(1, visitor.getUserName());
			statement.setString(2, visitor.getPassword());
			//Check that username and password is correct
			resultset = statement.executeQuery();
			//resultset.last();
			resultset.next();
			aRows=resultset.getRow();
			//resultset.beforeFirst();
			
			if(aRows==1){
				return 1;//Login Correct
			}
			else{
				System.out.println("hola");
				return 0;//Login Incorrect
			}
			
		}catch (Exception e) {
			System.out.println(" loginDAO 1: "+ e.getMessage());

		} finally {

		}
		return 0;
	}	

	

	public int insertVisitor(Visitor visitor, Connection con ){
				int aRows=0;
				ResultSet rs;
				
				//PreparedStatement statement = null;
				try{
					
					PreparedStatement statement = con.prepareStatement("select * from people where userName=?");
					statement.setString(1, visitor.getUserName());
					rs = statement.executeQuery();
					
				if(!rs.first()){

					statement = con.prepareStatement("insert into people(firstName,lastName,userName,email,dni,nPhone,address,password,isAdmind) values(?,?,?,?,?,?,?,?,?);");
					statement.setString(1,visitor.getFirstName());
					statement.setString(2,visitor.getLastName());
					statement.setString(3,visitor.getUserName());
					statement.setString(4,visitor.getEmail());
					statement.setString(5,visitor.getDni());
					statement.setString(6,visitor.getnPhone());
					statement.setString(7,visitor.getAddress());
					statement.setString(8,visitor.getPassword());
					statement.setBoolean(9,visitor.isAdmin());

					con.setAutoCommit(false);
					
					aRows = statement.executeUpdate();
					con.commit();
							}
				else{
					System.out.println("Already a username.");
				
				}
					
			}catch (SQLException e){
				System.out.println(" InsertVisitor: "+e.getMessage());
			}
				return aRows;
			}

	public int deleteVisitor(String visitor, Connection con ) {
		int aRows = 0;
		
		try{
			
			
			PreparedStatement statement = con.prepareStatement("delete from people where userName = ?");
			statement.setString(1, visitor);
			
			
			con.setAutoCommit(false);
			
			aRows = statement.executeUpdate();
			con.commit();
			
		}catch (SQLException e){
			System.out.println(" DeleteVisitor: "+e.getMessage());
		}
		
		return aRows;
	}
	
	public int updateVisitor(Visitor uVisitor, String userName, Connection con ) {
		int aRows = 0;
		
		try{
											
			PreparedStatement statement = con.prepareStatement("update people set firstName = ?, lastName = ?, email = ?, dni = ?, nPhone=?, address=? where userName=?");
			
			statement.setString(1,uVisitor.getFirstName());
			statement.setString(2,uVisitor.getLastName());
			statement.setString(3,uVisitor.getEmail());
			statement.setString(4,uVisitor.getDni());
			statement.setString(5,uVisitor.getnPhone());
			statement.setString(6,uVisitor.getAddress());
			statement.setString(7,userName);
			
			con.setAutoCommit(false);
			
			aRows = statement.executeUpdate();
			con.commit();
			
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
		
		return aRows;
	}
	
	public int updatePassword(String newPassword, String oldPassword, String userName, Connection con) {
		int aRows = 0;
		ResultSet rs;
		
		try{
			PreparedStatement statement = con.prepareStatement("select password from people where userName=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			rs.next();
			
			if(rs.getString("password") == oldPassword){
				
				PreparedStatement pstm = con.prepareStatement("update people set password = ? where userName = ?");
				pstm.setString(1, newPassword);
				pstm.setString(2, userName);
				
				con.setAutoCommit(false);
				aRows = pstm.executeUpdate();
				con.commit();
				
			}else{
				System.out.println("The password doesn't match");
			}
			
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
		
		return aRows;
	}
	
	public int getVisitorId(String userName, Connection con) {
		int peopleId = 0;
		ResultSet rs;
		
		try{
			PreparedStatement statement = con.prepareStatement("select peopleId from people where userName=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			rs.next();
			peopleId = rs.getInt("peopleId");
			
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
		
		return peopleId;
	}
	
	public int isAdmin (String userName, Connection con){
	int peopleAdmin = 0;
	ResultSet rs;
	
	try{
		PreparedStatement statement = con.prepareStatement("select isAdmind from people where userName=?");
		statement.setString(1, userName);
		rs = statement.executeQuery();
		rs.next();
		peopleAdmin= rs.getInt("isAdmind");
		
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
	return peopleAdmin;
	}
	
	public Visitor getVisitor(String userName, Connection con) {
		ResultSet rs;
		Visitor visitor = new Visitor();
		try{
			PreparedStatement statement = con.prepareStatement("select * from people where userName=?");
			statement.setString(1, userName);
			rs = statement.executeQuery();
			rs.next();
			visitor.setFirstName(rs.getString("firstName"));
			visitor.setLastName(rs.getString("lastName"));
			visitor.setUserName(rs.getString("userName"));
			visitor.setEmail(rs.getString("email"));
			visitor.setDni(rs.getString("dni"));
			visitor.setnPhone(rs.getString("nPhone"));
			visitor.setAddress(rs.getString("address"));
			visitor.setPassword(rs.getString("password"));
			visitor.setAdmin(rs.getBoolean("isAdmind"));	
		}catch (SQLException e){
			System.out.println(" UpdateVisitor: "+e.getMessage());
		}
		return visitor;
	}
}
	
	
